<?php
// api/students.php
header("Content-Type: application/json");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS");
header("Access-Control-Allow-Headers: Content-Type");

if ($_SERVER['REQUEST_METHOD'] == 'OPTIONS') { exit(0); }

require_once 'db.php';

$method = $_SERVER['REQUEST_METHOD'];
$studentID = $_GET['StudentID'] ?? null; // Matches form/JS

switch($method) {
    case "GET":
        try {
            if ($studentID !== null) {
                // Fetch single student JOINED with User and Department (optional but useful)
                $sql = "SELECT s.*, u.Username, d.DepartmentName
                        FROM Student s
                        LEFT JOIN User u ON s.StudentID = u.UserID
                        LEFT JOIN Department d ON s.DepartmentID = d.DepartmentID
                        WHERE s.StudentID = ?";
                $stmt = $pdo->prepare($sql);
                $stmt->execute([$studentID]);
                $student = $stmt->fetch();
                if ($student) {
                    echo json_encode($student);
                } else {
                    http_response_code(404);
                    echo json_encode(["error" => "Student not found"]);
                }
            } else {
                // Fetch all students JOINED
                 $sql = "SELECT s.*, u.Username, d.DepartmentName
                        FROM Student s
                        LEFT JOIN User u ON s.StudentID = u.UserID
                        LEFT JOIN Department d ON s.DepartmentID = d.DepartmentID
                        ORDER BY s.Name ASC";
                $stmt = $pdo->query($sql);
                $students = $stmt->fetchAll();
                echo json_encode($students);
            }
        } catch (PDOException $e) {
            http_response_code(500);
            echo json_encode(["error" => "Failed to retrieve student data", "details" => $e->getMessage()]);
        }
        break;

    case "POST":
        // Create BOTH User and Student in a transaction
        $data = json_decode(file_get_contents("php://input"), true);

        // Validate necessary fields from BOTH User and Student
        if (!isset($data['Username'], $data['Password'], $data['Name'], $data['DepartmentID'])
            || empty(trim($data['Username'])) || empty($data['Password']) || empty(trim($data['Name'])) || empty($data['DepartmentID'])) {
            http_response_code(400);
            echo json_encode(["error" => "Missing required fields (Username, Password, Name, DepartmentID)"]);
            exit;
        }
        // Add other validations (gender, date format, department existence if needed)

        $hashedPassword = password_hash($data['Password'], PASSWORD_DEFAULT);
        if ($hashedPassword === false) {
            http_response_code(500); echo json_encode(["error" => "Password hashing failed"]); exit;
        }

        $pdo->beginTransaction();
        try {
            // 1. Check for duplicate username
            $stmtCheck = $pdo->prepare("SELECT UserID FROM User WHERE Username = ?");
            $stmtCheck->execute([$data['Username']]);
            if ($stmtCheck->fetch()) {
                 $pdo->rollBack();
                 http_response_code(409);
                 echo json_encode(["error" => "Username already exists"]);
                 exit;
            }

            // 2. Insert User
            $stmtUser = $pdo->prepare("INSERT INTO User (Username, Password, Role) VALUES (?, ?, 'student')");
            $stmtUser->execute([$data['Username'], $hashedPassword]);
            $newUserID = $pdo->lastInsertId();

            // 3. Insert Student using the new UserID as StudentID
            $stmtStudent = $pdo->prepare("INSERT INTO Student (StudentID, Name, Gender, DepartmentID, EnrollmentDate) VALUES (?, ?, ?, ?, ?)");
            $stmtStudent->execute([
                $newUserID, // Use the UserID here
                trim($data['Name']),
                $data['Gender'] ?? 'male', // Default gender or validate
                $data['DepartmentID'],
                !empty($data['EnrollmentDate']) ? $data['EnrollmentDate'] : null // Handle optional date
            ]);

            // 4. Commit Transaction
            $pdo->commit();
            http_response_code(201);
            echo json_encode(["success" => true, "StudentID" => $newUserID]); // Return the ID

        } catch (PDOException $e) {
            $pdo->rollBack(); // Roll back changes on any error
            http_response_code(500);
             if ($e->getCode() == '23000') { // Check for foreign key violation (e.g., invalid DepartmentID)
                 http_response_code(400); // Bad request because DepartmentID might be wrong
                 echo json_encode(["error" => "Database constraint violation (e.g., invalid DepartmentID or duplicate entry)", "details" => $e->getMessage()]);
             } else {
                 echo json_encode(["error" => "Failed to add student", "details" => $e->getMessage()]);
             }
        }
        break;

    case "PUT":
        // Update ONLY Student table fields. User info (pwd, role) updated via users.php
        if ($studentID === null) {
            http_response_code(400); echo json_encode(["error" => "StudentID is required for update"]); exit;
        }
        $data = json_decode(file_get_contents("php://input"), true);

        // Validate fields to be updated
        if (!isset($data['Name']) || empty(trim($data['Name'])) || !isset($data['DepartmentID']) || empty($data['DepartmentID'])) {
             http_response_code(400);
             echo json_encode(["error" => "Missing required fields (Name, DepartmentID) for update"]);
             exit;
         }
        // Add other validations

        try {
            $stmt = $pdo->prepare("UPDATE Student SET Name = ?, Gender = ?, DepartmentID = ?, EnrollmentDate = ? WHERE StudentID = ?");
            $success = $stmt->execute([
                trim($data['Name']),
                $data['Gender'] ?? 'male',
                $data['DepartmentID'],
                !empty($data['EnrollmentDate']) ? $data['EnrollmentDate'] : null,
                $studentID
            ]);

             if ($success && $stmt->rowCount() > 0) {
                 echo json_encode(["success" => true, "message" => "Student updated"]);
            } elseif($success) {
                 http_response_code(404); // Or 200 OK with msg
                 echo json_encode(["error" => "Student not found or no changes made"]);
            } else {
                 http_response_code(500);
                 echo json_encode(["error" => "Failed to update student"]);
            }
        } catch (PDOException $e) {
            http_response_code(500);
             if ($e->getCode() == '23000') { // Check for foreign key violation (e.g., invalid DepartmentID)
                 http_response_code(400);
                 echo json_encode(["error" => "Database constraint violation (e.g., invalid DepartmentID)", "details" => $e->getMessage()]);
             } else {
                 echo json_encode(["error" => "Failed to update student", "details" => $e->getMessage()]);
             }
        }
        break;

    case "DELETE":
        // Delete BOTH Student and associated User in a transaction
        if ($studentID === null) {
            http_response_code(400); echo json_encode(["error" => "StudentID is required for deletion"]); exit;
        }

        $pdo->beginTransaction();
        try {
             // Foreign key constraints (ON DELETE CASCADE in User table for Student)
             // should handle related deletions automatically IF they point from User to Student.
             // However, our schema points Student->User. So, we might need manual delete order,
             // OR rely on the ON DELETE CASCADE FROM the User table delete.
             // Let's assume the User delete triggers the cascade based on schema.

            // Delete User (which should cascade to Student due to FOREIGN KEY constraint)
            $stmtUser = $pdo->prepare("DELETE FROM User WHERE UserID = ?");
            $successUser = $stmtUser->execute([$studentID]); // UserID == StudentID

            if ($successUser && $stmtUser->rowCount() > 0) {
                 $pdo->commit();
                 echo json_encode(["success" => true, "message" => "Student and associated user deleted"]);
            } else {
                 // If User wasn't found, maybe Student exists orphaned? Or both gone.
                 $pdo->rollBack(); // Rollback if user delete failed
                 http_response_code(404);
                 echo json_encode(["error" => "Student (or associated User) not found"]);
            }

        } catch (PDOException $e) {
            $pdo->rollBack();
             if ($e->getCode() == '23000') { // Foreign key constraint (e.g., Enrollments referencing Student)
                 http_response_code(409);
                 echo json_encode(["error" => "Cannot delete student: They have existing enrollment records.", "details" => $e->getMessage()]);
             } else {
                 http_response_code(500);
                 echo json_encode(["error" => "Failed to delete student", "details" => $e->getMessage()]);
             }
        }
        break;

    default:
        http_response_code(405);
        echo json_encode(["error" => "Method not allowed"]);
        break;
}
?>